﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Sworm
{
    public class Database
    {
        private readonly IDbConnection connection;
        public MetadataBuilder MetadataBuilder { get; private set; }
        public SqlDialect Dialect { get { return MetadataBuilder.Dialect; } }

        public Database(IDbConnection connection, MetadataBuilder metadata)
        {
            this.connection = connection;
            this.MetadataBuilder = metadata;
        }

        public Database(IDbConnection connection)
            : this(connection, MetadataBuilder.Default)
        {
        }

        public QueryOptions Query { get { return new QueryOptions(this); } }

        internal IDataReader ExecuteReader(IDbCommand command)
        {
            return command.ExecuteReader();
        }

        internal void ExecuteReader(string sql, IEnumerable<Param> parms, Action<DataReader> handler)
        {
            var command = CreateCommand();
            command.CommandText = sql;

            foreach (var p in parms)
            {
                var parm = command.CreateParameter();
                parm.ParameterName = p.Name;
                parm.Value = p.GetConvertedValue(MetadataBuilder) ?? DBNull.Value;
                command.Parameters.Add(parm);
            }

            using (var reader = ExecuteReader(command))
            {
                var wrapper = new DataReader(reader);
                handler(wrapper);
            }
        }

        public void ExecuteReader(string sql, Action<DataReader> handler)
        {
            ExecuteReader(sql, Enumerable.Empty<Param>(), handler);
        }

        private int ExecuteNonQuery(IDbCommand command)
        {
            return command.ExecuteNonQuery();
        }

        public int Insert(object record)
        {
            if (record == null)
            {
                throw new ArgumentNullException("record");
            }

            var table = MetadataBuilder.TableDataFor(record.GetType());

            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("insert into {0} (", table.Name.Escaped);

            var columns = table.Columns.ToList();
            bool isIdentity = table.OptionalData.HasAutoIncrementPK;
            if (isIdentity)
            {
                columns.Remove(table.OptionalData.PrimaryKey);
            }

            string comma = "";
            foreach (var column in columns)
            {
                sql.Append(comma).Append(column.Name.Escaped);
                comma = ", ";
            }

            var command = CreateCommand();

            sql.Append(") values (");

            int paramCount = 0;
            comma = "";
            foreach (var column in columns)
            {
                string paramName = "p" + paramCount++;
                sql.AppendFormat("{0}@{1}", comma, paramName);
                comma = ", ";
                CreateParameter(command, record, column, paramName);
            }

            sql.Append(");").AppendLine();

            if (isIdentity)
            {
                sql.AppendLine(Dialect.Sql_SelectInsertedRowId);
            }

            command.CommandText = sql.ToString();

            if (isIdentity)
            {
                using (var reader = ExecuteReader(command))
                {
                    reader.Read();
                    object val = reader.GetValue(0);
                    if (table.OptionalData.PrimaryKey.Member.Type == typeof(Int32))
                    {
                        val = Convert.ToInt32(val);
                    }
                    else if (table.OptionalData.PrimaryKey.Member.Type == typeof(Int64))
                    {
                        val = Convert.ToInt64(val);
                    }
                    table.OptionalData.PrimaryKey.Member.SetValue(record, val);
                    return reader.RecordsAffected;
                }
            }
            else
            {
                return ExecuteNonQuery(command);
            }
        }

        public int Delete(object record)
        {
            if (record == null)
            {
                throw new ArgumentNullException("record");
            }

            var table = MetadataBuilder.TableDataFor(record.GetType());
            var pk = table.RequirePrimaryKey();

            var command = CreateCommand();
            CreateParameter(command, record, pk, "pkVal");
            command.CommandText = string.Format(
                "delete from {0} where {1} = @pkVal",
                table.Name.Escaped, pk.Name.Escaped);

            return ExecuteNonQuery(command);
        }

        public int Execute(string sql)
        {
            var command = CreateCommand();
            command.CommandText = sql;
            return ExecuteNonQuery(command);
        }

        private IDbCommand CreateCommand()
        {
            var command = connection.CreateCommand();
            command.Transaction = CurrentTransactionOrNull;
            return command;
        }

        public int Update<T>(T record, Action<UpdateOptions<T>> options)
        {
            if (record == null)
            {
                throw new ArgumentNullException("record");
            }

            var table = MetadataBuilder.TableDataFor(record.GetType());
            var pk = table.RequirePrimaryKey();

            var opt = new UpdateOptions<T>(table);
            if (options != null)
            {
                options(opt);
            }

            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("update {0} set ", table.Name.Escaped);

            var command = CreateCommand();
            int paramCount = 0;
            string comma = "";
            foreach (var column in table.Columns)
            {
                if (column == pk)
                {
                    continue;
                }
                if (!opt.IsIncluded(column))
                {
                    continue;
                }

                string paramName = "p" + paramCount++;
                sql.AppendFormat("{0}{1} = @{2}", comma, column.Name.Escaped, paramName);
                comma = ", ";

                CreateParameter(command, record, column, paramName);
            }

            sql.AppendFormat(" where {0} = @pk;", pk.Name.Escaped);
            CreateParameter(command, record, pk, "pk");
            command.CommandText = sql.ToString();

            return ExecuteNonQuery(command);
        }

        public int Update(object record)
        {
            return Update(record, null);
        }

        private IDbDataParameter CreateParameter(IDbCommand command, object record, ColumnData column, string paramName)
        {
            object value = column.GetValue(record);
            value = column.PrepareParam(value);

            var parm = command.CreateParameter();
            parm.DbType = column.SqlType;
            parm.Direction = ParameterDirection.Input;
            parm.Value = value ?? DBNull.Value;
            parm.ParameterName = paramName;
            command.Parameters.Add(parm);
            return parm;
        }

        private readonly Stack<TransactionWrapper> transactions = new Stack<TransactionWrapper>();

        public IDbTransaction BeginTransaction(IsolationLevel level)
        {
            return Wrap(connection.BeginTransaction(level));
        }

        public IDbTransaction BeginTransaction()
        {
            return Wrap(connection.BeginTransaction());
        }

        private IDbTransaction Wrap(IDbTransaction tran)
        {
            var wrapper = new TransactionWrapper(tran, this);
            transactions.Push(wrapper);
            return wrapper;
        }

        // if making this public someday, be sure to expose the wrapper, not the wrappee
        private IDbTransaction CurrentTransactionOrNull
        {
            get { return transactions.Count == 0 ? null : transactions.Peek().wrapped; }
        }

        public int TransactionDepth { get { return transactions.Count; } }

        private class TransactionWrapper : IDbTransaction
        {
            internal readonly IDbTransaction wrapped;
            private readonly Database db;
            private bool wasCommitted = false;
            private bool wasRolledBack = false;
            private bool wasDisposed = false;

            public TransactionWrapper(IDbTransaction transaction, Database db)
            {
                this.wrapped = transaction;
                this.db = db;
            }

            public void Commit()
            {
                CleanUp();
                wrapped.Commit();
                wasCommitted = true;
            }

            public IDbConnection Connection
            {
                get { return wrapped.Connection; }
            }

            public IsolationLevel IsolationLevel
            {
                get { return wrapped.IsolationLevel; }
            }

            public void Rollback()
            {
                CleanUp();
                wasRolledBack = true;
                wrapped.Rollback();
            }

            public void Dispose()
            {
                try
                {
                    CleanUp();

                    if (wasDisposed)
                    {
                        return;
                    }
                    if (!wasRolledBack && !wasCommitted)
                    {
                        Rollback();
                    }
                }
                finally
                {
                    wrapped.Dispose();
                    wasDisposed = true;
                }
            }

            private void CleanUp()
            {
                if (db.transactions.Count > 0 && db.transactions.Peek() == this)
                {
                    db.transactions.Pop();
                }
            }
        }
    }
}
